﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;

/// <summary>
/// Summary description for D_Issue_Ap
/// </summary>
/// 
namespace Bestlife.ERP.DataAccess{

   
public class D_Issue_Ap{
    public static int lastid;
    public static Boolean result;
    public static decimal QuantityDB;
    public static string pid;
    public static Boolean status;

    public static Boolean status1;
	public D_Issue_Ap()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    //public static Boolean Issu_Ap(Bestlife.ERP.Entities.E_Issue_Ap idS,string user)
    //{

    //    Boolean status = false;
    //    DateTime date = System.DateTime.Now;

     
    //    String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
    //    Bestlife.ERP.DataAccess.Connection.connect(connStr);


    //    string sql = "UPDATE [Tbl_Issue_Master] SET [status]='"+idS.Status+ "' where [Issue_ID]= '"+idS.Issu_ID1+  "'";
    //    Boolean add = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);

    //    if (add)
    //    {
    //        status = true;
    //        Connection.con.Close();
    //        return status;
    //    }
    //    Connection.con.Close();
    //    return status;
    //}

    public static int lastissuid()
    {
 
            SqlDataReader rdR;
            try
            {
               
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT MAX(Issue_ID) as laststorid FROM [Tbl_ISSUE]";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    lastid = (int)rdR["laststorid"] + 1; 
                    
                }
                rdR.Close();
            }
            catch
            {
            }

             return lastid;          

        }

    public static Boolean insertioissurequest(Bestlife.ERP.Entities.E_Issue_Ap issue, string user)
    {
         Boolean status = false;
        try
        {
           
            DateTime date = System.DateTime.Now;

            String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
            Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
            //string result = Bestlife.ERP.BusinessRules.B_Issue_App.get_proname(issue.Pro_name1);
            string sql = "INSERT INTO [Tbl_Iss_Request] ([Issue_ID],[Pro_Name],[Pur_Price],[Description],[Sto_ID],[Qty],[User_Create],[Date_Create],[Pro_ID],[Code],[Branch]) VALUES('" + issue.Issu_ID1 + "','" + issue.Pro_name1 + "','" + issue.Cost + "','" + issue.Description + "','" + issue.Stor_ID + "','" + issue.Qty + "','" + user + "','" + date + "','" + issue.Pro_id + "','" + issue.Code + "','"+issue.Branch+"')";

            Boolean add = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);

            if (add)
            {
                status = true;
                Connection.con.Close();
                return status;
            }
            Connection.con.Close();
          
        }
             
        catch
        {
        }
        return status;
    }

    public static Boolean insertissu(Bestlife.ERP.Entities.E_Issue_Ap issu, string user)
    {
        SqlDataReader rdR;
        try
        {
            DateTime date = System.DateTime.Now;

            String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
            Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

            string sql1 = "select count(Pro_ID) as count from Tbl_Iss_Request where Issue_ID='" + issu.Issu_ID1 + "'";
            rdR = Connection.ReadTable(sql1);

            if (rdR.Read())
            {
                int count = (int)rdR["count"];
                rdR.Close();
                string sql = "INSERT INTO [Tbl_ISSUE]([Issue_ID],[Issue_Date],[TotalProduct],[Total],[User_Create],[Date_Create],[Branch]) VALUES('" + issu.Issu_ID1 + "','" + date + "','" + count + "','" + issu.Tot + "','" + user + "','" + date + "','"+issu.Branch+"')";
                Boolean add = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);
                if (add)
                {
                    Connection.con.Close();
                    rdR.Close();
                    result = true;

                }
                rdR.Close();
            }

        }
        catch
        {
            Connection.con.Close();


        }
        return result;

    }

    public static decimal lastissunot_approve()
    {
        SqlDataReader rdR;
        try
        {

            String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
            Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


            String Ssql = "SELECT MAX(Issue_ID) as lastid FROM Tbl_Iss_Request WHERE manger_status IS NULL";

            rdR = Connection.ReadTable(Ssql);

            if (rdR.Read())
            {
                lastid = Convert.ToInt32(rdR["lastid"].ToString());

            }
            rdR.Close();
        }
        catch
        {
        }
        return lastid;
    }


    public static Bestlife.ERP.Entities.product GetProuct(string pid, string code)
    {
        string pro_id = pid;
        string codex = code;
        SqlDataReader rdR;


        Bestlife.ERP.Entities.product pE = new Entities.product();


        try
        {
          
            String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
            Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

            String SelectProduct = "SELECT * from Tbl_Product p where p.Pro_ID='" + pro_id + "' AND p.Code='" + codex + "'";
            //SqlCommand act = new SqlCommand("UPDATE Bonus SET T_commision='" + tcom + "' WHERE User_id='" + id + "'", clsCon.conn_new);

            rdR = Connection.ReadTable(SelectProduct);
                      

            if (rdR.Read())
            {
                pE.Brand = rdR["Brand_Name"].ToString();
                pE.Bussiness_Value = decimal.Parse(rdR["Business_Value"].ToString());
                pE.Cat_ID = rdR["Cat_ID"].ToString();
                pE.Point_Value = decimal.Parse(rdR["Point_Value"].ToString());
                pE.Pro_Code = rdR["Code"].ToString();
                pE.Pro_Des = rdR["Description"].ToString();
                pE.Pro_ID = rdR["Pro_ID"].ToString();
                pE.Pro_Name = rdR["Pro_Name"].ToString();
                pE.Pro_Price = decimal.Parse(rdR["Pro_Price"].ToString());
                pE.Pur_Price = decimal.Parse(rdR["Pur_Price"].ToString());
                pE.Ret_Price = decimal.Parse(rdR["Ret_Price"].ToString());
               

               

                rdR.Close();
                Connection.con.Close();
            }
            rdR.Close();
        }
        catch (Exception ex) { }
        return pE;
    }

    public static Decimal ReturnStockQuantity(string Ids, string Code)
    {
        SqlDataReader rdR;
        try
        {
            String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
            Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


            String Ssql = "SELECT Qty_in_stock from Tbl_Stock  where Pro_ID='" + Ids + "' AND Code='" + Code + "' ";
            //SqlDataReader ldr = Bestlife.ERP.DataAccess.clsCon.ReadTable(Ssql);


            rdR = Connection.ReadTable(Ssql);


            if (rdR.Read())
            {
                QuantityDB = Decimal.Parse(rdR["Qty_in_stock"].ToString());
              
            }
            rdR.Close();
        }
        catch (Exception)
        {

        }
      

        return QuantityDB;
    }

    public static string stockid(string proid, string code)
    {
      
        try
        {
            SqlDataReader rdR;

            DateTime date = System.DateTime.Now;
            String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
            Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

            String Ssql = "SELECT  [Sto_ID] FROM [Tbl_Stock]  where Pro_ID='" + proid + "' and Code='" + code + "'";
            // String Ssql = "SELECT [Ret_Price] FROM [Tbl_Product] where [Pro_ID]='" + pro + "'";

            rdR = Connection.ReadTable(Ssql);
           
            if (rdR.Read())
            {

                 pid = rdR["Sto_ID"].ToString();
              


            }
            rdR.Close();
          



        }
        catch
        {

        }

        return pid;
    }

    public static Boolean ins_issustock(Bestlife.ERP.Entities.E_Issue_Ap issu,string user)
    {
        Boolean upd2 = false;
        try
        {

            DateTime date = System.DateTime.Now;
            Decimal QuantityinDB = Bestlife.ERP.DataAccess.D_Issue_Ap.issue_stock_quantity(issu.Pro_id,issu.Code);

            if (issu.Qty > 0)
            {
                Decimal QuantityToDB = QuantityinDB + Decimal.Parse(issu.Qty.ToString());
                if (QuantityToDB >= 0)
                {                   
                    String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                    Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                    string updtxt2 = "INSERT INTO [Tbl_Issue_Master]([Pro_Name],[P_Qty],[User_Create],[Date_Create],[Pro_ID],[Code]) VALUES('" + issu.Pro_name1 + "','" + issu.Qty + "','" + user + "','" + date + "','" + issu.Pro_id + "','" + issu.Code + "')";
                    //String updtxt2 = "INSERT INTO [Tbl_Stock_Master]([Pro_Name],[P_Qty],[User_Create],[Date_Create],[Pro_ID],[Code]) VALUES('" + product + "','" + qty + "','" + user + "','" + date + "','" + proname + "','" + code + "')";

                    //String updtxt2 = "UPDATE Bonus  SET  T_commision='999' WHERE User_id='001'";
                    Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(updtxt2);
                    if (edit1)
                    {
                        upd2 = true;
                    }
                }
                else
                {
                    //print we have only QuantityinDB now !!!
                }
            }
            else
            {
                // sory we have nt any Product 
            }
        }
        catch
        {
        }
        return upd2;
    }

    public static Decimal issue_stock_quantity(string Ids, string Code)
    {
        SqlDataReader rdR;
        try
        {
            String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
            Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


            String Ssql = "SELECT Qty_in_stock from Tbl_Issue_Stock  where Pro_ID='" + Ids + "' AND Code='" + Code + "' ";
            //SqlDataReader ldr = Bestlife.ERP.DataAccess.clsCon.ReadTable(Ssql);


            rdR = Connection.ReadTable(Ssql);


            if (rdR.Read())
            {
                QuantityDB = Decimal.Parse(rdR["Qty_in_stock"].ToString());

            }
            rdR.Close();
        }
        catch (Exception)
        {

        }


        return QuantityDB;
    }


    public static Boolean receivedissustock(Bestlife.ERP.Entities.E_Issue_Ap gn, string user, string approval, int itemcount)
    {
        try
        {
            SqlDataReader rdR;
            string code;
            string proname;
            DateTime date = System.DateTime.Now;

            String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
            Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
            //foreach (Bestlife.ERP.Entities.E_GRN gn in grn)
            //{
            string sql = "UPDATE [Tbl_Iss_Request] SET [manger_status] ='" + approval + "',[Last_Modify] ='" + date + "'  WHERE  [Issue_ID] ='" + gn.Issu_ID1 + "' and [Sto_ID]='" + gn.Stor_ID + "'";

            string sql1 = "UPDATE [Tbl_ISSUE] SET [status] ='" + approval + "',[Last_Modify] ='" + date + "'   WHERE Issue_ID='" + gn.Issu_ID1 + "' ";

            string sql2 = "SELECT [Code],[Pro_ID],[Pro_name] FROM [Tbl_Issue_Stock]  where [IssuSto_ID]='" + gn.Stor_ID + "'";
            Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql1);
            Boolean edit = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);
            rdR = Connection.ReadTable(sql2);
            if (edit && edit1 && rdR.Read())
            {
                code = rdR["Code"].ToString();
                proname = rdR["Pro_name"].ToString();
                rdR.Close();


                if (edit && edit1)
                {
                    status = true;
                    // Connection.con.Close();

                    Bestlife.ERP.DataAccess.D_Issue_Ap.addphystock(proname, gn.Qty, user, code, gn.Pro_id);
                }
                return status;


            }
            //}
        }
        catch
        {
        }
        return status;
    }

    public static Boolean addphystock(string proname, decimal qty, string user, string code,string proid)
    {
        Boolean upd2 = false;
        try
        {

            DateTime date = System.DateTime.Now;
            Decimal QuantityinDB = Bestlife.ERP.DataAccess.D_Issue_Ap.ReturnissuStockQuantity(proid, proname, code);

            if (qty > 0)
            {
                Decimal QuantityToDB = QuantityinDB + Decimal.Parse(qty.ToString());
                if (QuantityToDB >= 0)
                {

                    //string product = getproname(proname);
                    String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                    Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                    string updtxt2 = "INSERT INTO [Tbl_Issue_Master]([Pro_Name],[P_Qty],[User_Create],[Date_Create],[Pro_ID],[Code]) VALUES('" + proname + "','" + qty + "','" + user + "','" + date + "','" + proid + "','" + code + "')";
                    //String updtxt2 = "INSERT INTO [Tbl_Stock_Master]([Pro_Name],[P_Qty],[User_Create],[Date_Create],[Pro_ID],[Code]) VALUES('" + product + "','" + qty + "','" + user + "','" + date + "','" + proname + "','" + code + "')";

                    //String updtxt2 = "UPDATE Bonus  SET  T_commision='999' WHERE User_id='001'";
                    Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(updtxt2);
                    if (edit1)
                    {
                        upd2 = true;
                    }
                }
                else
                {
                    //print we have only QuantityinDB now !!!
                }
            }
            else
            {
                // sory we have nt any Product 
            }
        }
        catch
        {
        }
        return upd2;
    }

    public static Boolean allissuapprove(List<Bestlife.ERP.Entities.E_Issue_Ap> grn, string user, string approve, int itemcount)
    {
        try
        {
            SqlDataReader rdR;
            string code;
            string proname;
            DateTime date = System.DateTime.Now;

            String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
            Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

            foreach (Bestlife.ERP.Entities.E_Issue_Ap gn in grn)
            {

                string sql1 = "UPDATE [Tbl_Iss_Request] SET [manger_status] ='" + approve + "',[Last_Modify] ='" + date + "'  WHERE  [Issue_ID] ='" + gn.Issu_ID1 + "' and [Sto_ID]='" + gn.Stor_ID + "'";
                string sql = "UPDATE [Tbl_ISSUE] SET [status] ='" + approve + "',[Last_Modify] ='" + date + "'   WHERE Issue_ID='" + gn.Issu_ID1 + "' ";
                string sql2 = "SELECT [Code],[Pro_ID],[Pro_name] FROM [Tbl_Issue_Stock]  where [IssuSto_ID]='" + gn.Stor_ID + "'";

                Boolean edit = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql1);
               
                rdR = Connection.ReadTable(sql2);
                if (edit && rdR.Read())
                {
                    proname = rdR["Pro_name"].ToString();
                    code = rdR["Code"].ToString();
                    rdR.Close();


                    if (edit)
                    {
                         Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);
                        if(edit1)
                        {
                        //string proid = Bestlife.ERP.DataAccess.D_receivedstock.getproid(proname);
                        Boolean ss= Bestlife.ERP.DataAccess.D_Issue_Ap.addphystock(proname, gn.Qty, user, code, gn.Pro_id);
                      
                            if(ss)
                            {
                            decimal SK_Qty = Bestlife.ERP.DataAccess.D_Kandy.Sk_Stockcheck(gn.Pro_id, proname, code);
                        if (SK_Qty >= gn.Qty)
                        {
                            Boolean result = Bestlife.ERP.DataAccess.D_Kandy.upd_SK_Stock(gn.Qty, SK_Qty, user, gn.Pro_id, code);
                            if (result)
                                    {
                                        status1 = true;
                                    }
                                    else
                                    {
                                        status1 = false;
                                    }
                                }
                                else
                                {
                                    status1 = false;
                                }
                            }
                            else
                            {
                                status1 = false;
                            }
                        }
                        else
                        {
                            status1 = false;
                        }

                    }
                    else
                    {
                        status1 = false;
                    }

                }
                else
                {
                    status1 = false;
                }


            }
        }
        catch
        {
        }
        return status1;


    }

    public static Decimal ReturnissuStockQuantity(string pro_id,string proname,string code)
    {
        SqlDataReader rdR;


        try
        {
            String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
            Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
           // string product = getproname(pro_id);
            String Ssql = "SELECT Qty_in_stock from Tbl_Issue_Stock  where Pro_ID='" + pro_id + "' and Code='" + code + "' ";
            //SqlDataReader ldr = Bestlife.ERP.DataAccess.clsCon.ReadTable(Ssql);


            rdR = Connection.ReadTable(Ssql);


            if (rdR.Read())
            {
                QuantityDB = Decimal.Parse(rdR["Qty_in_stock"].ToString());

            }
            rdR.Close();
        }
        catch (Exception)
        {

        }

        return QuantityDB;

    }


    public static Boolean upd_SK_Stock(decimal qty, decimal SK_stock, string user, string proid, string code)
    {
        try
        {


            DateTime date = System.DateTime.Now;

            String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
            Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
            decimal newstock = SK_stock - qty;

            string sql1 = "UPDATE [Tbl_Stock]   SET [Qty_in_stock] ='" + newstock + "' Last_Modify='" + date + "' WHERE [Pro_ID]='" + proid + "' and [Code]='" + code + "'";



            Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql1);

            if (edit1)
            {

                status = false;
            }




        }
        catch
        {
        }
        return status;

    }
    }

 
}